package com.info.zhiduoduo.repository.mapper.system;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.account.ExportBusinessManagerDto;
import com.info.zhiduoduo.repository.dto.account.NumberOfEnterprisesDto;
import com.info.zhiduoduo.repository.dto.system.QueryBusinessManagerDto;
import com.info.zhiduoduo.repository.entity.system.BusinessManager;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;

/**
 * <p>
 * 商务经理表 Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2019-07-23
 */
public interface BusinessManagerMapper extends BaseMapper<BusinessManager> {

    @Select(" select * from zdd_business_manager " +
            " where " +
            " state = 1 " +
            " and if(isnull(#{merchandiser}),1 = 1,merchandiser = #{merchandiser})" +
            " and deleted = 0 " +
            " order by createtime ")
    List<BusinessManager> queryBusinessManagerByState(QueryBusinessManagerDto queryFormBean);

    @Select("select COUNT(*) from zdd_business_manager where business_manager = #{businessManager} and deleted = 0")
    int checkBusinessManager(@Param("businessManager") String businessManager);

    @Select("select DISTINCT(man.business_manager_id)  from zdd_business_manager man\n" +
            "where man.state=1 and man.deleted=0 and (man.business_manager_id in(select acc.business_manager_id from zdd_company_account acc) \n" +
            "or man.business_manager_id in(select par.business_manager_id from zdd_partner_account par)) and man.business_manager_id=${id}\n")
    List<BusinessManager> judgeBusinessManager(@Param("id") Integer id);
    //获取金额
    @Select("SELECT COUNT(DISTINCT total.compid) AS compid,years AS years,total.business_manager_id,business_manager AS businessManager, " +
            "             SUM(needurlcount) AS needurlcount,    " +
            "             SUM(haveurlcount) AS haveurlcount,   " +
            "             SUM(zddAmount) AS zddAmount,    " +
            "             SUM(crowdsourceAmount)AS crowdsourceAmount,    " +
            "             SUM(entrustAmount)AS entrustAmount,    " +
            "             SUM(groupAmount)AS groupAmount    " +
            "             FROM (SELECT ca.business_manager_id,cp.compid AS compid,com.compname,bm.business_manager,bm.nickname,    " +
            "             DATE_FORMAT(cp.createtime,'%Y-%m') AS years,SUM(CASE WHEN cp.attachment_url IS NULL OR cp.attachment_url ='' THEN 0 ELSE 1 END) haveurlcount,    " +
            "             COUNT(cp.projectid) needurlcount,    " +
            "             IFNULL(SUM(cp.projectdealamount),0) AS zddAmount,    " +
            "             0 AS crowdsourceAmount,    " +
            "             0 AS entrustAmount,    " +
            "             0 AS groupAmount    " +
            "             FROM zdd_company_project cp    " +
            "             LEFT JOIN zdd_company_account ca ON ca.compid = cp.compid    " +
            "             LEFT JOIN `zdd_business_manager` bm ON ca.business_manager_id = bm.business_manager_id    " +
            "             LEFT JOIN zdd_company com ON com.compid=cp.compid    " +
            "             WHERE cp.state IN (10,12,13,15,20) AND bm.business_manager_id IS NOT NULL    " +
            "             GROUP BY ca.business_manager_id,cp.compid,DATE_FORMAT(cp.createtime,'%Y-%m')    " +
            "             UNION ALL    " +
            "             SELECT ca.business_manager_id,pro.compid AS compid,com.compname,bm.business_manager,bm.nickname,    " +
            "             DATE_FORMAT(pro.createtime,'%Y-%m') AS years, " +
            "             SUM(CASE WHEN pro.project_file_url IS NULL OR pro.project_file_url ='' THEN 0 ELSE 1 END) haveurlcount,    " +
            "             COUNT(DISTINCT pro.projectid) needurlcount,    " +
            "             0 AS zddAmount,    " +
            "             IFNULL(SUM(pro.amount), 0) AS crowdsourceAmount,    " +
            "             0 AS entrustAmount,    " +
            "             0 AS groupAmount    " +
            "             FROM (SELECT csp.project_file_url,csp.compid,csp.createtime,csp.projectid,IFNULL(SUM(cspi.integrated_tax_fee), 0) AS amount    " +
            "             FROM zdd_crowdsource_project_item cspi    " +
            "             JOIN zdd_crowdsource_project csp ON csp.projectid = cspi.projectid AND csp.state NOT IN (98,99)    " +
            "             JOIN zdd_crowdsource_trade_record_item cstri ON cstri.project_item_id = cspi.id AND repay = 0 AND cstri.status IN (1,2,3)    " +
            "              WHERE cspi.deleted = 0 GROUP BY csp.projectid)pro    " +
            "             LEFT JOIN zdd_company_account ca ON ca.compid = pro.compid    " +
            "             LEFT JOIN zdd_business_manager bm ON ca.business_manager_id = bm.business_manager_id    " +
            "             LEFT JOIN zdd_company com ON com.compid=pro.compid    " +
            "             GROUP BY ca.business_manager_id,pro.compid,DATE_FORMAT(pro.createtime,'%Y-%m')    " +
            "             UNION ALL    " +
            "             SELECT ca.business_manager_id,pro.compid AS compid,com.compname,bm.business_manager,bm.nickname,    " +
            "             DATE_FORMAT(pro.createtime,'%Y-%m') AS years, " +
            "             0 haveurlcount,    " +
            "             0 needurlcount,    " +
            "             0 AS zddAmount,    " +
            "             0 AS crowdsourceAmount,    " +
            "             IFNULL(SUM(epi.tax_inclusive_amount), 0) AS entrustAmount,    " +
            "             0 AS groupAmount    " +
            "             FROM zdd_entrustment_project_item epi    " +
            "             JOIN zdd_entrustment_project pro ON epi.import_id = pro.id    " +
            "             LEFT JOIN zdd_company_account ca ON ca.compid = pro.compid    " +
            "             LEFT JOIN zdd_business_manager bm ON ca.business_manager_id = bm.business_manager_id    " +
            "             LEFT JOIN zdd_company com ON com.compid=pro.compid    " +
            "             WHERE pro.project_type IN (5,6) AND epi.state IN(2,3,4)    " +
            "             GROUP BY ca.business_manager_id,pro.compid,DATE_FORMAT(epi.createtime,'%Y-%m')    " +
            "             UNION ALL    " +
            "             SELECT ca.business_manager_id,pro.compid AS compid,com.compname,bm.business_manager,bm.nickname,    " +
            "             DATE_FORMAT(pro.createtime,'%Y-%m') AS years, " +
            "             0 haveurlcount,    " +
            "             0 needurlcount,    " +
            "             0 AS zddAmount,    " +
            "             0 AS crowdsourceAmount,    " +
            "             0 AS entrustAmount,    " +
            "             IFNULL(SUM(epi.tax_inclusive_amount), 0) AS groupAmount    " +
            "             FROM zdd_entrustment_project_item epi    " +
            "             JOIN zdd_entrustment_project pro ON epi.import_id = pro.id    " +
            "             LEFT JOIN zdd_company_account ca ON ca.compid = pro.compid    " +
            "             LEFT JOIN zdd_business_manager bm ON ca.business_manager_id = bm.business_manager_id    " +
            "             LEFT JOIN zdd_company com ON com.compid = pro.compid    " +
            "             WHERE pro.project_type IN (7,8) AND epi.state IN(2,3,4)    " +
            "             GROUP BY ca.business_manager_id,pro.compid,DATE_FORMAT(epi.createtime,'%Y-%m')    " +
            "             ) total    " +
            "             WHERE total.business_manager_id IS NOT NULL    " +
            "             AND business_manager LIKE '%${name}%'    " +
            "             AND IF(ISNULL(DATE_FORMAT(#{years},'%Y-%m')),1=1,total.years= DATE_FORMAT(#{years},'%Y-%m'))    " +
            "             GROUP BY total.years,total.business_manager_id ")
    List<ExportBusinessManagerDto> exportBusinessManager(@Param("years") Date yearss, @Param("name") String name);
    //获取根据id和时间获取本月企业总数
    @Select("SELECT COUNT(DISTINCT ca.compid) AS compNum " +
            "             FROM zdd_company_account ca     " +
            "             JOIN zdd_company com ON ca.compid = com.compid     " +
            "             LEFT JOIN zdd_business_manager bm ON ca.business_manager_id = bm.business_manager_id     " +
            "             WHERE ca.business_manager_id = #{id}    " +
            "             and DATE_FORMAT(ca.createtime,'%Y-%m') = #{years}" +
            "             GROUP BY ca.business_manager_id")
    Integer getCompNum(@Param("id") int id ,@Param("years") String years);
    //获取根据id和时间获取本月渠道数
    @Select("SELECT count(distinct partnerid) as parNum " +
            "             from zdd_partner_account pa " +
            "             where business_manager_id = #{id}  " +
            "             and partner_type =1  " +
            "             and DATE_FORMAT(pa.createtime,'%Y-%m') = #{years}  " +
            "             GROUP BY pa.business_manager_id")
    Integer getParNum(@Param("id") int id ,@Param("years") String years);
    //根据id和时间获取本月跟进和拜访的次数
    @Select("  SELECT cf.type,COUNT(cf.cust_id) as bcNum" +
            "        from zdd_crm_following cf " +
            "        left join zdd_crm_customer cc on cf.cust_id = cc.cust_id " +
            "        left join zdd_crm_system_manager man on cc.cust_person = man.managerid " +
            "        where cc.cust_person is not null  " +
            "        and man.managerid is not null  " +
            "        and type is not null " +
            "        and cc.status = 1 and cf.cust_id != 0 " +
            "        and cf.cust_id is not null " +
            "        and man.business_manager_id = #{id}  " +
            "        and DATE_FORMAT(cf.createtime,'%Y-%m') = #{years}  " +
            "        GROUP BY man.managerid,cf.type,DATE_FORMAT(cf.createtime,'%Y-%m')")
    List<NumberOfEnterprisesDto> getFrequency(@Param("id") int id , @Param("years") String years);

}
